﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics.CodeAnalysis;
using System.Globalization;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading;
using Loki.Utils;

namespace Loki.Data.OleDb
{
    /// <summary>
    /// OLE DB database adapter.
    /// </summary>
    public abstract class OleDatabaseAdapter : LokiObject, IDatabaseAdapter
    {
        public virtual void Initialize()
        {
        }

        public abstract bool Initialized
        {
            get;
        }

        protected abstract string GetRequest(string P_CommandName);

        public string ConnectionString
        {
            get;
            set;
        }

        public string CurrentUser
        {
            get
            {
                return Environment.UserName;
            }
        }

        public IDatabaseCommand CreateCommand(string P_Name)
        {
            return new OleDatabaseCommand(P_Name);
        }

        public IDatabaseCommand CreateCommand(string P_Name, CommandType P_Type)
        {
            return new OleDatabaseCommand(P_Name);
        }

        #region IDatabaseAdapter Members

        private string _DataSouceDescription = string.Empty;

        private const string FILE_SOURCE_REGEX = "Source=(.?);";

        private const string REGEX_GROUP_1 = "$1";

        public string DataSourceDescription
        {
            get
            {
                if (string.IsNullOrEmpty(_DataSouceDescription))
                {
                    Regex L_FileSource = new Regex(FILE_SOURCE_REGEX);
                    string L_DataSouceDescription = L_FileSource.Replace(ConnectionString, REGEX_GROUP_1);
                    Interlocked.CompareExchange(ref _DataSouceDescription, L_DataSouceDescription, string.Empty);
                }

                return _DataSouceDescription;
            }
        }

        /// <summary>
        /// Executes command.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "The request is a parametrized request")]
        public virtual void ExecuteNonQuery(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (OleDbCommand L_Command = new OleDbCommand())
            {
                L_Command.CommandText = GetRequest(P_Command.Name);
                L_Command.Transaction = P_Transaction as OleDbTransaction;
                L_Command.Connection = L_Command.Transaction.Connection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                L_Command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "The request is a parametrized request")]
        public virtual IDataReader ExecuteQuery(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (OleDbCommand L_Command = new OleDbCommand())
            {
                L_Command.CommandText = GetRequest(P_Command.Name);
                L_Command.Transaction = P_Transaction as OleDbTransaction;
                L_Command.Connection = L_Command.Transaction.Connection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                return L_Command.ExecuteReader();
            }
        }

        /// <summary>
        /// Executes the query and return an unique value.
        /// </summary>
        /// <param name="P_Command">The command.</param>
        /// <param name="P_Transaction">The transaction.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "The request is a parametrized request")]
        public virtual object ExecuteScalar(IDatabaseCommand P_Command, IDbTransaction P_Transaction)
        {
            using (OleDbCommand L_Command = new OleDbCommand())
            {
                L_Command.CommandText = GetRequest(P_Command.Name);
                L_Command.Transaction = P_Transaction as OleDbTransaction;
                L_Command.Connection = L_Command.Transaction.Connection;
                L_Command.Parameters.AddRange(P_Command.Parameters.ToArray());
                L_Command.CommandType = P_Command.Type;

                return L_Command.ExecuteScalar();
            }
        }

        private const string SELECT_ALL = "SELECT * FROM {0}";

        /// <summary>
        /// Bulks insert data in a table of the same name.
        /// </summary>
        /// <param name="P_Table">The table.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "No user request in the table parameter")]
        public int BulkInsert(DataTable P_Table)
        {
            using (OleDbDataAdapter L_Adapter = new OleDbDataAdapter(string.Format(CultureInfo.InvariantCulture, SELECT_ALL, P_Table), ConnectionString))
            {
                OleDbCommandBuilder L_Builder = new OleDbCommandBuilder(L_Adapter);
                try
                {
                    L_Builder.GetInsertCommand();
                    return L_Adapter.Update(P_Table);
                }
                finally
                {
                    L_Builder.Dispose();
                }
            }
        }

        #endregion

        #region Transactions
        /// <summary>
        /// Open transaction on current database (new connection opened).
        /// </summary>
        /// <returns>New transaction.</returns>
        [SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope", Justification = "Can not dispose the connection while the transaction is alive")]
        public IDbTransaction OpenTransaction()
        {
            OleDbConnection L_Conn = null;
            try
            {
                L_Conn = new OleDbConnection(ConnectionString);
                L_Conn.Open();
                return L_Conn.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            catch (Exception)
            {
                if (L_Conn != null)
                {
                    L_Conn.Dispose();
                }

                throw;
            }
        }

        /// <summary>
        /// Commits the transaction.
        /// </summary>
        /// <param name="P_Tran">Transaction to commit.</param>
        public bool CommitTransaction(IDbTransaction P_Tran)
        {
            P_Tran.Commit();
            if (P_Tran.Connection != null && P_Tran.Connection.State != ConnectionState.Closed)
            {
                P_Tran.Connection.Close();
            }

            return true;
        }

        /// <summary>
        /// Rollbacks the transaction.
        /// </summary>
        /// <param name="P_Tran">Transaction to rollback.</param>
        public bool RollbackTransaction(IDbTransaction P_Tran)
        {
            if (P_Tran != null)
            {
                P_Tran.Rollback();
                if (P_Tran.Connection != null && P_Tran.Connection.State != ConnectionState.Closed)
                {
                    P_Tran.Connection.Close();
                }

                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
    }
}